Olist SQL
E-Commerce Analysis

Which sellers drive growth — and is late delivery killing retention?

Olist is a Brazilian e-commerce marketplace connecting small sellers to major retail channels. Leadership needs data-driven answers to two strategic questions: Where is revenue concentrated, and are operational failures (late deliveries) eroding the customer relationships that drive repeat business?

Olist Brazilian E-Commerce Dataset

The dataset is a real-world e-commerce database from Olist, containing multiple relational tables covering orders, products, customers, sellers, payments, order items, and reviews. All analysis was conducted in PostgreSQL 17 with pgAdmin 4.

Advanced SQL applied to real business questions

-- Top 10 sellers by revenue with cumulative market share
WITH seller_revenue AS (
  SELECT
    oi.seller_id,
    SUM(oi.price) AS total_revenue,
    COUNT(DISTINCT oi.order_id) AS total_orders
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY oi.seller_id
)
SELECT
  seller_id,
  total_revenue,
  total_orders,
  DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank,
  ROUND(
    total_revenue * 100.0 / SUM(total_revenue) OVER(), 2
  ) AS market_share_pct
FROM seller_revenue
ORDER BY revenue_rank
LIMIT 10;

What the SQL analysis revealed

Strategic actions from the SQL findings

• Implement a seller reliability score that weights delivery performance heavily. Use the late delivery vs. review score relationship as the business case — every delivery failure has a quantifiable review impact.
• Build a top seller retention program focused on the top 50 sellers by revenue. These accounts represent disproportionate platform revenue and should receive account management attention and priority logistics support.
• Invest in Health and Beauty seller acquisition and logistics capacity. The MoM growth data makes this the clearest category expansion opportunity.
• Analyze boleto users as a distinct customer segment for premium product merchandising. Their higher average order value suggests different purchasing intent that the platform should capitalize on.